package dao.impl;

import dao.UserDao;
import model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * The type User dao.
 */
public class UserDaoImpl implements UserDao {
    /**
     * The Pstmt.
     */
    PreparedStatement pstmt = null;
    /**
     * The Rs.
     */
    ResultSet rs = null;

    @Override
    public int insert(User user) {
        int count = 0;
        String sql = "insert into user (username,password,status,email,sex,phone,uurl,state) values (?,?,?,?,?,?,?,1)";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setLong(3, user.getStatus());
            pstmt.setString(4, user.getEmail());
            pstmt.setLong(5, user.getSex());
            pstmt.setString(6, user.getPhone());
            pstmt.setString(7, user.getUurl());
            count = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count;
    }

    @Override
    public int delete(int id) {
        int count = 0;
        String sql = "UPDATE user set state=0 WHERE uid= ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            count = pstmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count;
    }

    @Override
    public int update(User user) {
        int count = 0;
        String sql = "UPDATE user set username = ?,password = ? ,email = ?,sex = ?,phone = ?,uurl = ? where uid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getEmail());
            pstmt.setLong(4, user.getSex());
            pstmt.setString(5, user.getPhone());
            pstmt.setString(6, user.getUurl());
            pstmt.setLong(7, user.getUid());
            count = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count;
    }

    @Override
    public User findById(long id) {
        User user = null;
        String sql = "SELECT * FROM user WHERE uid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUid(id);
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setStatus(rs.getLong("status"));
                user.setEmail(rs.getString("email"));
                user.setSex(rs.getLong("sex"));
                user.setPhone(rs.getString("phone"));
                user.setUurl(rs.getString("uurl"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return user;
    }

    @Override
    public List<User> findAll(int pageIndex, int pageSize) {
        List<User> list = new ArrayList<>();
        String sql = "SELECT * FROM user WHERE state=1 LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (pageIndex - 1) * pageSize);
            pstmt.setInt(2, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUid(rs.getLong("uid"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setStatus(rs.getLong("status"));
                user.setEmail(rs.getString("email"));
                user.setSex(rs.getLong("sex"));
                user.setPhone(rs.getString("phone"));
                user.setUurl(rs.getString("uurl"));
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int pageCount() {
        int count = 0;
        String sql = "SELECT count(*) from user WHERE state=1";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int findByName(String name) {
        int count = -1;
        String sql = "SELECT uid FROM user WHERE username = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int findByPhone(String phone) {
        int count = -1;
        String sql = "SELECT uid FROM user WHERE phone = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, phone);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int findByEmile(String email) {
        int count = -1;
        String sql = "SELECT uid FROM user WHERE email = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, email);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public List<User> findByPartName(String partName, int pageIndex, int pageSize) {
        List<User> list = new ArrayList<>();
        String sql = "SELECT * FROM user where username like ? LIMIT ?,? ";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + partName + "%");
            pstmt.setInt(2, (pageIndex - 1) * pageSize);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUid(rs.getLong("uid"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setStatus(rs.getLong("status"));
                user.setEmail(rs.getString("email"));
                user.setSex(rs.getLong("sex"));
                user.setPhone(rs.getString("phone"));
                user.setUurl(rs.getString("uurl"));
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }


    @Override
    public User login(String username, String password) {
        User user = null;
        String sql = "SELECT * FROM user WHERE username = ? AND password = ? AND state=1";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUid(rs.getLong("uid"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setStatus(rs.getLong("status"));
                user.setEmail(rs.getString("email"));
                user.setSex(rs.getLong("sex"));
                user.setPhone(rs.getString("phone"));
                user.setUurl(rs.getString("uurl"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return user;
    }

    @Override
    public int pageCountByName(String username) {
        int count = 0;
        String sql = "SELECT count(*) from user like ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery();
            pstmt.setString(1, "%" + username + "%");
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int findIdByName(String username) {
        int uid = 0;
        String sql = "SELECT uid FROM user WHERE status = 1 and username = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                uid = rs.getInt("uid");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return uid;
    }

}
